如果用户所在的公司，给予了用户直接访问公司数据库权限，这是获取数据的理想来源。不仅可以保证访问的是最新的数据，而且从数据库加载数据通常比从文件中加载数据效率更高。

Power Query 支持连接到多种数据库，并且无需安装任何其他驱动程序。连接方式：在 Excel 或 Power BI 用户界面以下三个单独区域可以找到。

如果用户找不到需要连接的数据库，先不要失望。待用户安装了供应商的 ODBC 驱动程序，用户将能够通过【自其他源】连接到数据库【ODBC】。

由于连接到大多数数据库的步骤非常相似，因此将先连接到【Microsoft Access 数据库】。这是一个 SQL 数据库，托管在微软的 Azure web services 上，这意味着无论用户身处世界何处，都可以连接并浏览其中的数据。

在本例中，将连接到【AdventureWorks 数据库】，并按地区分析其公司每年的总销售额。

要开始前，需要完成如图 12-1 所示步骤。

图 12-1 连接到【Azure 数据库】

此时，系统将提示用户输入凭据来连接到数据库。会注意到这里有几个选项。

默认选项是使用用于登录计算机的【Windows 凭据】。如果用户是在公司内部局域网的数据库上工作，并且 IT 部门允许【Windows】身份验证，那么这可能对用户有效。但是，最好的做法是联系所在公司的 IT 团队，以便他们能够为用户提供连接到数据源所需的所有服务器、数据库和访问级别权限。

用户还可以在同一选项卡上提供另一组【Windows 凭据】。如果需要使用一组不同的用户凭据连接到数据库，这将非常有用。

但是，要正确连接到本节介绍的【Azure 数据库】，用户需要切换到对话框的【数据库】选项卡，因为作者在创建用户 ID 时考虑的是数据库安全性，而不是 Windows 安全性。在该选项卡上，用户需要执行如下 3 项操作。

如下图所示，当用户输入正确的验证信息之后，单击【连接】按钮，如图 12-2 所示。

图 12-2 使用【数据库】安全凭据连接到数据库

如果输入错误 “连接名称、数据库、用户 ID 或密码”，并需要修改它们，则可以通过执行以下步骤进行修改。

将启动【数据源设置】对话框，如图 12-3 所示。

图 12-3 【数据源设置】界面，找到了 “Adventure”

虽然【当前工作簿中的数据源】视图只包含当前工作簿中的源，但随着时间的推移，【全局权限】视图可能会变得非常拥挤，因此使用搜索窗格对数据进行筛选非常实用。在上面的图片中，已经通过筛选找到了数据源 “Adventure”，因为在这里知道它是【Azure 数据库】URL 的一部分。

在这里用户有如下三个选择。

单击【编辑权限】按钮将允许用户查看凭据类型，如图 12-4 所示。

图 12-4【Azure 数据库】的数据源设置

如果需要，用户还可以通过单击【凭据】下面的【编辑】按钮来触发新窗口来更新 / 替换【用户名】和【密码】。

在这里特别选择在【Windows Azure】上托管数据库，是因为需要使其运行时间尽可能接近 100% 正常运行时间，并且自本书出版的第一个版本以来一直都在这样做。在此期间，收到了一些无法连接到【Azure 数据库】的用户的支持请求。这些问题最常见的原因如下所示。

如果用户遇到对【Azure 数据库】的访问被阻止的情况，本书还将在示例文件中展示包含【AdventureWorks 数据库】的微软 Access 版本。不使用【SQL Azure】连接器，只需使用【Access 数据库】连接器即可。用户会发现连接这些数据库的步骤几乎是一样的。

一旦 Power Query 连接到数据库，用户将被带到【导航器】界面，该界面将允许用户选择要连接到的表。在本例中，本书希望从 “SalesOrders” 表中提取一些数据。由于表太多，这里将使用搜索功能缩小列表范围。

Power Query 会从数据库选定表格中截取部分样本数据，并将它们呈现在预览窗格中，以便用户大致了解存储在该表中的数据，如图 12-5 所示。

图 12-5 使用【导航器】

这里的数据看起来相当有用，单击【转换数据】，看看可以从中收集到哪些有用的信息。

这里用户会注意到的第一件事是，【应用的步骤】窗口中有两个步骤：“Source” 和 “Navigation”。如果选择 “Source” 步骤，用户将看到它返回到数据库的原始模式，允许用户查看数据库中存在哪些其他的表、视图和对象。然后，“Navigation” 步骤钻取到选定的表中。

用户会注意到的第二件事是这里有很多数据。此时需要按如下操作步骤及来缩小数据范围。

此时结果将如图 12-6 所示。

图 12-6 清理后 “SalesOrderHeader” 表

大多数列标题都很有意义，但最后两列有一些重要的内容。这些列没有显示 “SalesOrderHeader” 表中的值，而是显示数据库中其他表中的相关值。

这是连接到数据库的一大好处：大多数数据库都支持自动关系检测，允许用户浏览相关记录，而无需自己设置关系或执行任何合并。但为什么 “SalesLT.Customer” 列显示 “Value”，而 “SalesLT.SalesOrderDetail” 列显示 “Table” 呢？

如果检查实际的数据库结构，就会发现 “Customers” 表在 “Customer” 表和 “SalesOrderHeader” 表之间定义了一对多关系（虽然一个客户可能有多个销售订单，但每个销售订单只能有一个客户）。这里看到的 “Value” 实际上是 “Customer” 表中的一条记录，单条记录中包含该单个客户的所有相关字段，如图 12-7 所示。

图 12-7 “SalesLT.Customer” 列包含每个订单对应的客户相关记录

相反，如果用户预览 “SalesOrderDetail” 列的第一个表，可以看到主订单下包含该订单全部子订单的详细信息，如图 12-8 所示。

图 12-8 销售订单 “SO71774” 有多行记录，表示销售的不同的产品

虽然 “SalesOrderHeader” 表和 “SalesOrderDetail” 表之间的关系也是一对多的，但在本例中，唯一值位于连接到的 “SalesOrderHeader” 表上。

此时来缩小数据范围，并进行更多的数据清洗，如下所示。

此时数据将如图 12-9 所示。

图 12-9 将不同表格里的数据合并到了一起，但是却没有创建任何连接（Join），是不是很神奇

现在是完成这个查询并使用数据的时候了，在使用 Excel 时，将进行如下操作。

加载数据后，将快速构建一个数据透视表来汇总数据。

结果是一个很规整的数据透视表，用户可以随时【刷新】，如图 12-10 所示。

图 12-10 从 Windows【Azure SQL 数据库】创建的数据透视表

这个解决方案的优点在于，用户还可以向工作表中添加切片器、数据透视图和其他项目，按用户的需要显示数据。然而这个方案最好的地方在于，用户只需要通过一个简单的单击步骤【数据】【全部刷新】就能够在任何时间按需刷新线上数据库的实时数据并更新解决方案。

数据库提供的一个重要特性是能够利用查询折叠来优化查询性能。当使用 Power Query 的用户界面构建解决方案时，该技术是内置的，并且在默认情况下是可以正常使用，但用户也可能意外地将其关闭，导致查询仅由 Excel 处理。为了理解如何避免这个错误，用户需要了解什么是查询折叠，以及它是如何在默认情况下工作的。

当用户单击各种命令来选择、过滤、排序和分组数据时，并不总是考虑背后发生的事情。正如用户现在所知道的，这些步骤中的每一步都记录在名为【应用的步骤】窗口中，这让用户可以构建一个有顺序的宏。但是，用户可能不知道的是，Power Query 还会尽可能多的将这些命令转换为数据库的本机查询语言（SQL），并将它们发送到数据库。

更令人惊奇的是，具有查询折叠功能的服务器将接受这些单独的查询，然后尝试将它们折叠成更高效的查询。当需要发出连续的查询指令时，这个折叠查询的作用会尤为显现。例如：用户在 “选择所有表中的记录” 指令之后紧接着又要求 “请排除 150 以外的所有部门”。

用外行的话说，服务器不是加载全部 1,000,000 条记录，然后筛选到该部门的 1,500 条记录，而是通过查询来构建一个更高效的查询，该查询的内容如下：

这样做的影响是巨大的，因为它节省了处理 998,500 条记录的处理时间。虽然并非所有命令都可以折叠，但很多命令都可以折叠，从而将处理工作负载推送到服务器上。那么用户怎么知道它是否有效呢？返回上一个查询，将会注意到在每个步骤的右击菜单上都有一个【查看本机查询】选项。此处显示的是【删除其他列】步骤的视图，如图 12-11 所示。

图 12-11 查看将发送到 SQL 数据库的查询

当然，这在执行的转换中相对较早，但可以看到查询折叠一直持续到 “Replaced Value” 步骤，如图 12-12 所示。

图 12-12 用户界面驱动的步骤仍在 “折叠” 到一个整合的 SQL 语句中

只要【查看本机查询】命令可用，就可以确保查询折叠仍在进行。此时来看看在图 12-13 中的 “Changed Type” 步骤上发生了什么。

图 12-13 【查看本机查询】视图已显示为灰色

此时，【查看本机查询】选项变灰，表示此步骤可能不会折叠到 SQL 语句中。当 Power Query 执行刷新时，它将从上一步检索 SQL 语句，然后使用本地处理器和 RAM 继续其余步骤。

此时迫不及待地想在 Excel 和 Power BI 桌面中看到的一个新功能是查询折叠指示器。下图取自 Power Query 在线版，显示了针对【Azure 数据库】的（不同）查询中步骤旁边的查询折叠指示器，如图 12-14 所示。

图 12-14 即将到来的查询折叠指示器

这些指示器比当前鼠标单击查询步骤并显示本机查询的方法要更直观，它们可以立刻向用户展示，从 “Navigation” 开始一直到 “Changed column type” 查询一直在被折叠，但是 “Kept bottom rows” 步骤开始查询折叠链就被打破了。

由于 Power Query 连接器的列表一直在增长，用户无法真正提供支持或不支持查询折叠的连接器的全面列表。话虽如此，关于查询折叠，需要认识到的一件重要事情是，它是一种有效地将处理工作下压到数据源的技术，这意味着在数据源处需要一个计算引擎才能工作。这就是为什么用户通常会看到查询折叠在同时具有存储和计算引擎的数据库中工作。虽然很希望 “TXT”,“CSV” 以及 Excel 文件能够有这个功能，但很遗憾，这些文件都不包含计算引擎。一般来说，“文件不会折叠”。即如果用户正在连接到文件，则查询折叠将不可用。另一方面，如果要连接到数据库，则可以使用查询折叠。

“文件不会折叠” 规则的一个显著例外是微软 Access。Access 尽管是文件，但也支持查询折叠。话虽如此，由于文件通常托管在本地 PC 上，因此性能提升可能不如在服务器上折叠一个完整的数据库那么明显。

用户还应该知道，并非所有数据库都支持查询折叠。如果用户连接的数据库没有查询折叠功能，那么 Power Query 将下载完整的数据集，并使用自己的引擎执行请求的步骤来处理它们。虽然最终也能得到一样的结果，但效率可能会非常低。

同样值得理解的是，并非所有数据库连接器都是平等创建的。一个特别需要注意的数据连接器是 ODBC。如果没有其他选择，ODBC 绝对是可用的，但 ODBC 连接器本质上是一个 “一刀切” 的连接器。这意味着它还没有针对用户的特定数据集进行优化，可能很容易打破查询折叠。只有在没有本机（或自定义）连接器可用的情况下，才应该使用 ODBC 连接到数据库，本书建议用户仔细观察每一个步骤的查询折叠（通过右击查询【查看本机查询】）。

根据本书的经验，在高效地从数据库中提取数据以及查询折叠技术方面似乎存在一些常见的混淆。这里解释其中的一些误解。

误解 1：从数据库中提取数据的最有效方法是生成一个 SQL 查询，该查询在一个 Power Query 步骤中完成所有工作。

虽然在实际操作中这种方法并不可取，但它确实存在。在一个案例中，看到了一个解决方案中，顾问采用了这种方法，构建了一个非常复杂的 SQL 语句和使用【高级选项】窗口在连接器中对其进行硬编码。它起作用了吗？是的，事实上这个查询工作得很快。顾问能否编写比 Power Query 更高效的 SQL 吗？答案是可以的。

但是，由于业务需求的变化，客户需要修改它。当然，客户聘请顾问的原因是他们不具备 SQL 知识。根据顾问的建议，客户现在要么花费更多的费用聘请顾问来修改它，要么客户自己学会修改 SQL 语句（事实证明，客户花了几个小时来做后者）。

现在，客户是否可以通过用户界面对逻辑进行修改，作为一个新的步骤？是的可以的，但是由于查询是从一个自定义 SQL 步骤开始的，因此查询折叠会立即中断，这可能会对性能产生很大影响。

那么，如果顾问一开始就通过用户界面构建了查询呢？客户只需要几分钟就能做出所需的更改，而且他们可以更快地恢复正常工作。

误解 2：打破查询折叠的步骤的顺序总是会打破查询折叠。不幸的是（或许是幸运的），事情并不像那样黑白分明。事实上，Power Query 正在使用一种算法来生成 SQL 代码，有时步骤的顺序会导致 Power Query 无法创建有效的 SQL 语句。在这种情况下，查询折叠将被中断。

当查询折叠中断时，不要立即放弃。尝试更改步骤的顺序，看看是否有效果（要执行此操作，请右击【应用的步骤】窗口下的步骤，然后选择【前移】或【后移】）。有时，步骤顺序的改变会使查询折叠提示再次亮起。

误解 3：必须有人维护一个列表，列出哪些步骤可以折叠。这是一个非常普遍的误解。在这里将很抱歉地告诉各位用户，没有这样的名单，而且以后也不会有。为什么？这是因为哪些命令能折叠可能取决于 Power Query 连接器，也可能取决于正在执行的步骤的顺序。使用自己的公司数据库次数越多，虽然没有涵盖所有场景的列表，但用户就会开始了解配置中哪些项会破坏查询折叠。

误解 4：查询折叠不会在一连串的查询中持续存在，这并不正确。如果是这样，就会对在第 2 章中概述的查询结构产生一些严重的质疑。如果用户想证明查询折叠可以在多个查询中持续存在，那么很容易做到。

现在，用户已经创建了一个从 “Database” 到 “OrdersBySalesPerson” 的两步查询链。此时先回到 “OrdersBySalesPerson” 并右击 “Replaced Value（替换的值）” 步骤。【查看本机查】询仍然可选，说明查询折叠没有因为多出来的查询分支而被打破。

误解 5：只要用户能连接数据库，那么用什么连接器都无所谓。这个小神话差点毁了用户的一个 BI 项目。他们的 IT 部门教他们使用 ODBC 连接器连接到 SQL 数据库，而不是 Power Query 的内置 SQL Server 连接器。

起初，一切都很好。客户连接到数据库，立即进行筛选，检索前一周的～100 万行数据，并构建解决方案来解决所有问题。他们热爱 Power Query，过着美好的生活。但随着时间的推移，解决方案变得越来越慢，甚至在整整一天的 8 小时的工作日内都无法刷新完成。

事实证明，问题在于 IT 部门教用户通过他们安装和配置的 ODBC 连接器将 Power Query 连接到微软 SQL Server。这个是一个如此大的问题的原因是，当筛选行时，这种技术组合会导致查询折叠中断。这对用户来说是一个巨大的冲击，因为这似乎是一个肯定会起作用的步骤，但有了它，刷新就是将整个数百万行数据库下载到 Excel 的内存中，然后试图减少数据量。

如何来解决这个问题呢？重新启动查询以使用 SQL 数据库连接器而不是 ODBC。就这样，用户更改了连接器，并保持查询的其余部分不变。最后看到的是，查询在几分钟内在就可以【刷新】完成。

根据用户使用 Power Query 过程的深入程度，用户可能会遇到声明数据源【隐私级别】的提示。那么，它们是什么？它们为什么重要？事实上，它们与查询折叠及其工作方式密切相关。

关于查询折叠的一个常见误解是，可以看到的每个本机查询都会被发送到数据库。这不是真的，实际上提交到数据库查询只是最后一个有效的本机查询。话虽如此，每个查询都会向数据库提交两个本机查询。

虽然这在技术上可能并不完美，但用户可以将首次提交的目的视为向数据库发送任何参数，用来检索可能驱动当前变量（如 “最近日期”、“第一个客户名称”）的预览。Power Query 随后将更新、折叠和编译其查询，并将其发送到数据库进行检索。

理解这一点很重要的原因是，根据用户构建查询的方式，一些数据可能会被发送到数据源。这显然会引起一些担忧，因为用户不希望意外泄漏敏感数据，特别是当它超出用户公司的控制网络时。

Power Query 团队对此问题的回答是允许用户为其每一个数据源声明一个【隐私级别】，一共有三个类型的数据【隐私级别】，如下所示。

在理想情况下，用户需要正确标记每一个数据源，以便此系统能够保护用户的数据源，使用户不会将关键和敏感的信息发送到不应该被其他用户知道的数据源，或者意外泄漏机密数据、将机密数据暴露到用户无法控制的数据源。

【隐私级别】的工作方式是，无论何时，当用户试图连接不同数据源时，Power Query 都会检查每个数据源的【隐私级别】是否兼容，规则如下所示。

理论上，如果 Excel 电子表格标记为【组织】，则无法将数据从电子表格发送到公共网站。另一方面，如果用户的电子表格被标记为【公共】，并且用户要将其发送到声明为【组织】的公司数据库，那么这就是可以的，因为此类标记数据可以从公共源发送到组织源。

实际上，处理【隐私级别】可能非常具有挑战性。如果用户的理解不正确，则会收到一条关于公式的令人沮丧的消息，如图 12-15 所示 “公式防火墙” 错误。

图 12-15 没有什么比 Power Query 中的这个错误更令人沮丧的了

设置【隐私级别】并不困难。一旦需要，Power Query 查询提示如图 12-16 所示界面。

图 12-16 弹窗提示用户对当前查询设置【隐私级别】信息

重要的是要认识到隐私设置也不限于使用数据库。它是一种保护用户不在任何数据源之间意外泄漏数据的机制。图 12-16 实际上是在使用一个函数将 “FilesList” 查询连接到 “SharePoint”，这会触发黄色提示来声明【隐私级别】。单击【继续】将进入对话框，用户可以在其中为工作簿中的每个数据源设置【隐私级别】。

如果需要查看或修改数据【隐私级别】，可以在管理解决方案【凭据】的同一位置执行此操作。作为提醒，用户可以通过以下方式找到这些设置。

【数据源设置】选择数据源，【编辑权限】。

此时，用户可以更改特定连接的【隐私级别】，如图 12-17 所示。

图 12-17 管理【Azure 数据库】的【隐私级别】

虽然【隐私级别】方法的目的很好（谁想意外泄漏数据？），但使用这个方法有时可能会感觉很麻烦。在最好的情况下，隐私级别设置也会严重影响性能，而在最坏的情况下，它会导致 “公式防火墙” 错误，根本无法直接刷新数据。

考虑如图 12-18 所示的表，它显示两个查询的 5 个【刷新】操作时间的平均值。

图 12-18 比较两个基于 Excel 的查询的【刷新】时间

关于这些查询，需要了解的重要一点是，如果它们都是存储在同一工作簿中的表，用户就没必要在合并这些数据源时检查它们的【隐私级别】，因为所有数据都存在于同一个文件中。如果用户将这些数据与数据库结的数据合并起来，那么用户可能需要检查这些数据的【隐私级别】。当然，如果所有数据都来自同一来源，检查这项设置对用户其实没有任何附加价值。

尽管如此，用户可以看到结果相当清楚，禁用隐私引擎后进行【刷新】总是更快，有时甚至更明显。

在禁用隐私引擎之前，重要的是要考虑这样做的利弊，如表 12-1 所示。

表 12-1 禁用 Power Query 隐私引擎的利弊

如果用户将公司数据源与公司无法控制的网络托管或外部数据源相结合，则可能会使公司的数据面临风险。此外，如果用户打算发布 Power BI 模型并安排刷新，则很可能会发现 Power BI 服务不允许用户覆盖隐私设置。

另一方面，如果用户正在构建 100% 包含在公司网络中的解决方案，禁用隐私引擎可能会带来一些非常重要的好处。第一个是开发解决方案更容易，因为用户不必担心 “公式防火墙”（将在本书后面讨论），第二个是性能将得到提高。

隐私设置通过【查询选项】对话框进行控制，可通过以下单击路径访问该对话框。

用户还会发现此界面中有两个【隐私】选项卡：一个在【全局】区域，另一个在【当前工作簿】的部分。首先来查看【全局】区域，如图 12-19 所示。

图 12-19 查看 Excel 中的【全局】区域【隐私】选项，如你所见，这里有 3 个选项

本书不建议用户使用最后一个选项的原因是，它会设置用户在没有收到警告的情况下意外地将【专用】级别数据泄漏到公共数据源（这类似于在 Excel 2003 及更早版本中关闭 VBA 宏安全性，来避免每次都弹出警告提示。这是可行的，但这样作用户将会完全暴露专用数据源）。本书不希望看到任何用户为了从 Power Query 中获得更好的性能而采取自杀式的行为。

本书主张根据实际情况逐一对每个解决方案做出是否关闭隐私功能的决定，而不是在【全局】范围内关闭隐私功能。这将保护用户的数据，并强制用户在更改设置之前评估每个数据源。用户可以通过【当前工作簿】区域下的【隐私】选项卡执行此操作，如图 12-20 所示。

图 12-20 管控【隐私级别】的正确位置

此窗口中的第一个【隐私】选项是默认行为，它将提示用户声明所有未指定数据源的【隐私级别】。它还将在合并数据时强制执行隐私检查（无论用户是否觉得需要）。

第二个【隐私】选项允许用户禁用特定解决方案的【隐私级别】，这是本书建议用户在控制【隐私级别】时使用的路径。

Power Query 查询可能很慢，尤其是当用户必须启用隐私检查时。这是一个不幸的事实，微软非常清楚这一点，并一直在努力改进。有鉴于此，本书必须制定一些策略，尽可能最大限度地提高效率。

如果觉得对你有帮助，就点个赞同呗，十分感谢！

我的知乎号主页也会经常分享很多关于PowerBI 优质内容，以及我平时直播预告信息，可以点击我号主页查看。